"""
Case Type   : 极致RTO支持备机可读
Case Name   : 资源池化存储复制双集群，主集群创建行存段页式interval区表，备集群开启极致RTO场景下验证备机可读
Create At   : 2025/03
Owner       : haomeng
Description :
    1、备集群内开启极致RTO特性，重启使其生效
    2、主集群内创建行存段页式interval分区表，插入数据
    3、备集群首备节点查看表数据
    4、备集群从备节点查看表数据
    5、对测试表进行部分DML操作，稍后再次在备集群首备节点中查看表数据
    6、清理环境，恢复环境初始状态
Expect      :
    1、设置参数成功，重启成功
    2、创建成功，插入数据成功
    3、查看成功，与主集群内查看数据一致
    4、查看失败，从备节点不可读
    5、DML操作成功，再次查看表数据，与主集群内查看数据一致
    6、清理成功，恢复环境成功
History     :
"""

import os
import time
import unittest

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger
from yat.test import Node
from yat.test import macro


class DisasterRTOStandbyRead(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.p_pri_sh = CommonSH('P_PrimaryDbUser')
        self.s_pri_sh = CommonSH('S_PrimaryDbUser')
        self.s_sta_sh = CommonSH('S_Standby1DbUser')
        self.constant = Constant()

        self.param_list = ['recovery_parse_workers', 'recovery_redo_workers', 'ss_disaster_mode']
        self.value_list = ['4', '4']
        self.default_value = []
        self.table = 'ss_t_rto_004'
        self.table_new = 'ss_t_rto_004_new'

        self.log.info('---校验当前环境是否为存储复制双集群---')
        mode = self.p_pri_sh.execut_db_sql(f'show {self.param_list[2]};')
        self.log.info(mode)
        self.assertIn('dorado', mode, '当前环境不是存储复制双集群')

    def test_rto_standby_read(self):
        text = '-----step1:集群内开启极致RTO特性，重启使其生效;  expect:设置成功，重启成功-----'
        self.log.info(text)

        for param in self.param_list[:2]:
            value = self.s_pri_sh.execut_db_sql(f'show {param}')
            self.default_value.append(value.splitlines()[-2].strip())
        self.log.info(self.default_value)

        for param in self.param_list[:2]:
            set_param = self.s_pri_sh.execute_gsguc('set',
                                                    self.constant.GSGUC_SUCCESS_MSG,
                                                    f'{param} = {self.value_list[self.param_list.index(param)]}')
            self.log.info(set_param)
            self.assertTrue(set_param, '参数设置失败')

        self.log.info('---重启集群使参数生效---')
        restart = self.s_sta_sh.restart_db_cluster()
        self.log.info(restart)
        self.assertTrue(restart, '集群重启失败')

        self.log.info('---查看参数值修改是否成功---')
        for param in self.param_list[:2]:
            value = self.s_pri_sh.execut_db_sql(f'show {param};')
            self.assertEqual(value.splitlines()[-2].strip(),
                             self.value_list[self.param_list.index(param)], '参数未修改成功')

        text = '-----step2:主集群内创建行存段页式interval分区表，插入数据  expect:创建成功，插入数据成功-----'
        self.log.info(text)

        sql1 = f"drop table if exists {self.table} cascade;" \
               f"create table {self.table}(id int, name varchar(200), age int, tel int, birth date)" \
               f"partition by range(birth)" \
               f"interval ('1 year')" \
               f"(partition p1 values less than('1970-01-01')," \
               f" partition p2 values less than('1980-01-01')," \
               f" partition p3 values less than('1990-01-01')," \
               f" partition p4 values less than('2000-01-01')," \
               f" partition p5 values less than('2010-01-01')," \
               f" partition p6 values less than('2020-01-01'));" \
               f"declare" \
               f"    i integer;" \
               f"begin" \
               f"    for i in 1..100000 loop" \
               f"    insert into {self.table} values(random()*100,'name_'||random()*10,random()*100,random()*1000000," \
               f"    to_timestamp(to_char(current_date - random()*365*interval '1 year','YYYY-MM-DD'),'YYYY-MM-DD'));" \
               f"    end loop;" \
               f"end;"
        self.log.info(sql1)

        res1 = self.p_pri_sh.execut_db_sql(sql1)
        self.log.info(res1)
        self.assertIn(self.constant.CREATE_TABLE_SUCCESS, res1, '建表失败')
        self.assertIn(self.constant.CREATE_ANONYMOUS_BLOCK_SUCCESS_MSG, res1, '匿名块执行失败，插入数据失败')

        text = '-----step3:备集群首备节点查看表数据  expect:查看成功-----'
        self.log.info(text)

        self.log.info('---等待备集群数据同步---')
        flag = self.s_pri_sh.check_data_consistency()
        self.assertTrue(flag, '数据同步失败')

        sql2 = f"select count(*) from {self.table};"
        self.log.info(sql2)
        select_res = self.s_pri_sh.execut_db_sql(sql2)
        self.log.info(select_res)
        self.assertIn('100000', select_res.splitlines()[-2].strip(), '查询结果不一致')

        text = '-----step4:备集群从备节点查看表数据  expect:查看失败,从备节点不可读-----'
        self.log.info(text)

        ss_select = self.s_sta_sh.execut_db_sql(sql2)
        self.log.info(ss_select)
        self.assertIn(self.constant.disaster_read_fail_msg, ss_select, '从备节点查询成功')

        text = '-----step5:主集群对测试表进行部分DML操作，稍后再次在备机中查看表数据  expect:查看成功，与主机数据一致-----'
        self.log.info(text)

        sql3 = f"alter table {self.table} rename to {self.table_new};" \
               f"alter table {self.table_new} add new_col1 int, add new_col2 text first," \
               f" add new_col3 float after new_col2;" \
               f"update {self.table_new} set tel = tel * 2 where id%2 != 0 and id < 1000;" \
               f"delete from {self.table_new} where id < 20;"
        self.log.info(sql3)
        res3 = self.p_pri_sh.execut_db_sql(sql3)
        self.log.info(res3)
        self.assertEqual(2, res3.count(self.constant.ALTER_TABLE_MSG), 'alter表失败')
        self.assertIn(self.constant.UPDATE_SUCCESS_MSG, res3, 'update表数据失败')
        self.assertIn(self.constant.DELETE_SUCCESS_MSG, res3, 'delete表数据失败')

        sql4 = f"select * from {self.table_new} limit 10;" \
               f"select count(*) from {self.table_new};"
        self.log.info(sql4)

        pri_res = self.p_pri_sh.execut_db_sql(sql4)
        self.log.info('===主集群查看表数据结果===' + pri_res)

        self.log.info('---等待备集群数据同步---')
        flag = self.s_pri_sh.check_data_consistency()
        self.assertTrue(flag, '数据同步失败')

        sta_res = self.s_pri_sh.execut_db_sql(sql4)
        self.log.info('===备集群查看表数据结果===' + sta_res)
        self.assertEqual(pri_res.splitlines()[-2], sta_res.splitlines()[-2], '查询结果不一致')

    def tearDown(self):
        text = '-----step6:清理环境，恢复环境初始状态  expect:清理成功，恢复环境成功-----'
        self.log.info(text)

        drop = f"drop table {self.table_new} cascade;"
        self.log.info(drop)
        drop_res = self.p_pri_sh.execut_db_sql(drop)
        self.log.info(drop_res)

        for param in self.param_list[:2]:
            reset_param = self.s_pri_sh.execute_gsguc('set',
                                                      self.constant.GSGUC_SUCCESS_MSG,
                                                      f'{param} = {self.default_value[self.param_list.index(param)]}')
            self.log.info(reset_param)

        restart = self.s_pri_sh.restart_db_cluster(False)
        self.log.info(restart)

        for param in self.param_list[:2]:
            reset_value = self.s_pri_sh.execut_db_sql(f'show {param};')
            self.log.info(reset_value)
            self.assertEqual(reset_value.splitlines()[-2].strip(),
                             self.default_value[self.param_list.index(param)], '参数恢复失败')
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, drop_res, '删除表失败')
        self.log.info(f'----{os.path.basename(__file__)} end----')
